
***********************;
* FFS MCR Beneficiary *;
***********************;
%macro mbsf(startyr, endyr);
%do year= &startyr %to &endyr;
	%IF &year>2013 %then %do;
		DATA mbsf; set cms.Mbsf_abcd_summary_&year; year = &year;
			rename AGE_AT_END_REF_YR=age; rename SEX_IDENT_CD=gender; rename ENTLMT_RSN_CURR=BENE_ENTLMT_RSN_CURR;
			keep BENE_ID year AGE_AT_END_REF_YR SEX_IDENT_CD ZIP_CD BENE_BIRTH_DT BENE_DEATH_DT COVSTART ENTLMT_RSN_CURR BENE_HI_CVRAGE_TOT_MONS BENE_SMI_CVRAGE_TOT_MONS BENE_STATE_BUYIN_TOT_MONS BENE_HMO_CVRAGE_TOT_MONS
				MDCR_ENTLMT_BUYIN_IND_01-MDCR_ENTLMT_BUYIN_IND_12 HMO_IND_01-HMO_IND_12 ZIP_CD;
		RUN;
	%END;
	%ELSE %do;
		DATA mbsf; set cms.Mbsf_ab_summary_&year; year = &year; 
			rename BENE_ZIP_CD = ZIP_CD; rename BENE_AGE_AT_END_REF_YR=age; rename BENE_SEX_IDENT_CD=gender;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_01 = MDCR_ENTLMT_BUYIN_IND_01; rename BENE_HMO_IND_01=HMO_IND_01;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_02 = MDCR_ENTLMT_BUYIN_IND_02; rename BENE_HMO_IND_02=HMO_IND_02;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_03 = MDCR_ENTLMT_BUYIN_IND_03; rename BENE_HMO_IND_03=HMO_IND_03;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_04 = MDCR_ENTLMT_BUYIN_IND_04; rename BENE_HMO_IND_04=HMO_IND_04;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_05 = MDCR_ENTLMT_BUYIN_IND_05; rename BENE_HMO_IND_05=HMO_IND_05;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_06 = MDCR_ENTLMT_BUYIN_IND_06; rename BENE_HMO_IND_06=HMO_IND_06;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_07 = MDCR_ENTLMT_BUYIN_IND_07; rename BENE_HMO_IND_07=HMO_IND_07;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_08 = MDCR_ENTLMT_BUYIN_IND_08; rename BENE_HMO_IND_08=HMO_IND_08;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_09 = MDCR_ENTLMT_BUYIN_IND_09; rename BENE_HMO_IND_09=HMO_IND_09;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_10 = MDCR_ENTLMT_BUYIN_IND_10; rename BENE_HMO_IND_10=HMO_IND_10;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_11 = MDCR_ENTLMT_BUYIN_IND_11; rename BENE_HMO_IND_11=HMO_IND_11;
			rename BENE_MDCR_ENTLMT_BUYIN_IND_12 = MDCR_ENTLMT_BUYIN_IND_12; rename BENE_HMO_IND_12=HMO_IND_12;
			keep BENE_ID year BENE_AGE_AT_END_REF_YR BENE_SEX_IDENT_CD BENE_ZIP_CD BENE_BIRTH_DT BENE_DEATH_DT COVSTART BENE_ENTLMT_RSN_CURR BENE_HI_CVRAGE_TOT_MONS BENE_SMI_CVRAGE_TOT_MONS BENE_STATE_BUYIN_TOT_MONS BENE_HMO_CVRAGE_TOT_MONS 
				BENE_MDCR_ENTLMT_BUYIN_IND_01-BENE_MDCR_ENTLMT_BUYIN_IND_12 BENE_HMO_IND_01-BENE_HMO_IND_12;
		RUN;
	%END;

	* Append all years;
	%IF &year=&startyr %then %do;
		DATA mbsf_total; set mbsf; RUN;
	%END;
	%ELSE %do;
		PROC append base=mbsf_total data=mbsf force; RUN;
	%END;

	/*
	* Reshape Monthly;
	PROC sort data=mbsf; by BENE_ID ZIP_CD; RUN;
	PROC transpose data=mbsf out=long1 prefix=BENE_MDCR_ENTLMT_BUYIN_IND_;
		by Bene_ID ZIP_CD;
		var MDCR_ENTLMT_BUYIN_IND_01-MDCR_ENTLMT_BUYIN_IND_12;
	RUN;
	DATA long1; set long1; month = substr(_NAME_,23,2); RUN;
	PROC transpose data=mbsf out=long2 prefix=HMO_IND_ ;
		by Bene_ID ZIP_CD;
		var HMO_IND_01-HMO_IND_12;
	RUN;
	DATA long2; set long2; month = substr(_NAME_,9,2); RUN;
	DATA mbsf2;  merge long1 long2; by BENE_ID month; keep Bene_ID ZIP_CD month BENE_MDCR_ENTLMT_BUYIN_IND_1 HMO_IND_1; RUN;
	DATA mbsf_full_&year; set mbsf2; mon = input(month, 2.); year = &year; RUN;
	DATA mbsf_ffs_&year; set mbsf2; if BENE_MDCR_ENTLMT_BUYIN_IND_1~="0" and HMO_IND_1="0"; mon = input(month, 2.); year = &year; RUN;
	PROC sort data=mbsf_ffs_&year; by bene_id mon; run;
	PROC datasets library=work; delete mbsf mbsf2 long1 long2; RUN;
	*/
%END;
%mend mbsf;
%mbsf(2006, 2015);



***********************;
* Payment by Category *;
***********************;
%macro MCRPayment(year); 
	%IF &year>2013 %then %let k= k;
	%IF &year<=2013 %then %let k= j;
	* MedPar - Inpatient & SNF;
	PROC FREQ DATA = cms.Medpar_all_file_&year; TABLES NCH_CLM_TYPE_CD; RUN;
	DATA temp1; set cms.Medpar_all_file_&year; keep BENE_ID DSCHRG_DT NCH_CLM_TYPE_CD mon payment; mon = month(DSCHRG_DT); payment = MDCR_PMT_AMT+PASS_THRU_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	DATA inpatient; set temp2; if NCH_CLM_TYPE_CD="60"; RUN;
	PROC SQL;
		CREATE table inpatient_sum as
		SELECT BENE_ID, sum(payment) as payment_inpatient
		FROM inpatient
		GROUP by BENE_ID;
	QUIT;
	DATA SNF; set temp2; if NCH_CLM_TYPE_CD="20" or NCH_CLM_TYPE_CD="30"; RUN;
	PROC SQL;
		CREATE table SNF_sum as
		SELECT BENE_ID, sum(payment) as payment_SNF
		FROM SNF
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2 inpatient SNF; RUN;

	* Home Health Agency;
	PROC FREQ DATA = cms.hha_base_claims_&k._&year; TABLES CLM_SRVC_CLSFCTN_TYPE_CD; RUN;
	DATA temp1; set cms.hha_base_claims_&k._&year; keep BENE_ID CLM_THRU_DT CLM_SRVC_CLSFCTN_TYPE_CD mon payment; mon = month(CLM_THRU_DT); payment = CLM_PMT_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	DATA HHA_A; set temp2; if CLM_SRVC_CLSFCTN_TYPE_CD="3";
	PROC SQL;
		CREATE table HHA_A_sum as
		SELECT BENE_ID, sum(payment) as payment_HHA_A
		FROM HHA_A
		GROUP by BENE_ID;
	QUIT;
	DATA HHA_B; set temp2; if CLM_SRVC_CLSFCTN_TYPE_CD="2";
	PROC SQL;
		CREATE table HHA_B_sum as
		SELECT BENE_ID, sum(payment) as payment_HHA_B
		FROM HHA_B
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2 HHA_A HHA_B; RUN;

	* Hospice;
	DATA temp1; set cms.hospice_base_claims_&k._&year; keep BENE_ID CLM_THRU_DT payment mon; mon = month(CLM_THRU_DT); payment = CLM_PMT_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	PROC SQL;
		CREATE table hospice_sum as
		SELECT BENE_ID, sum(payment) as payment_hospice
		FROM temp2
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2; RUN;

	* Carrier;
	PROC FREQ DATA = cms.bcarrier_line_&k._&year; TABLES NCH_CLM_TYPE_CD; RUN;
	DATA temp1; set cms.bcarrier_line_&k._&year; keep BENE_ID CLM_THRU_DT NCH_CLM_TYPE_CD HCPCS_CD HCPCS_1ST_MDFR_CD HCPCS_2ND_MDFR_CD payment mon; mon = month(CLM_THRU_DT); payment = LINE_NCH_PMT_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	DATA carrier; set temp2; if NCH_CLM_TYPE_CD="71"; RUN;
	DATA carrier_DME; set temp2; if NCH_CLM_TYPE_CD="72"; RUN;
	PROC datasets library=work; delete temp1 temp2; RUN;

	* DME;
	PROC FREQ DATA = cms.dme_line_&k._&year; TABLES NCH_CLM_TYPE_CD; RUN;
	DATA temp1; set cms.dme_line_&k._&year; keep BENE_ID CLM_THRU_DT NCH_CLM_TYPE_CD HCPCS_CD HCPCS_1ST_MDFR_CD HCPCS_2ND_MDFR_CD payment mon; mon = month(CLM_THRU_DT); payment = LINE_NCH_PMT_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	DATA DME_non; set temp2; if NCH_CLM_TYPE_CD="81"; RUN;
	DATA temp; set carrier DME_non; RUN;
	PROC SQL;
		CREATE table carrier_sum as
		SELECT BENE_ID, sum(payment) as payment_carrier
		FROM temp
		GROUP by BENE_ID;
	QUIT;

	DATA temp; set temp2; if NCH_CLM_TYPE_CD="82"; RUN;
	DATA DME; set carrier_DME temp; RUN;
	PROC sort data = hcpcs_list_matched; by HCPCS_CD HCPCS_1ST_MDFR_CD HCPCS_2ND_MDFR_CD; RUN;
	PROC sort data=DME; by HCPCS_CD HCPCS_1ST_MDFR_CD HCPCS_2ND_MDFR_CD; RUN;
	DATA temp; merge DME (in = in1) hcpcs_list_matched (in = in2); by HCPCS_CD HCPCS_1ST_MDFR_CD HCPCS_2ND_MDFR_CD; if in1; RUN;
	DATA DME1; set temp; if category_x=""; RUN;
	PROC SQL;
		CREATE table DME1_sum as
		SELECT BENE_ID, sum(payment) as payment_DME_nCBP
		FROM DME1
		GROUP by BENE_ID;
	QUIT;
	DATA DME2; set temp; if category_x~=""; RUN;
	PROC SQL;
		CREATE table DME2_sum as
		SELECT BENE_ID, sum(payment) as payment_DME_CBP
		FROM DME2
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2 DME DME1 DME2; RUN;

	* Outpatient;
	DATA temp1; set cms.outpatient_base_claims_&k._&year; keep BENE_ID CLM_THRU_DT payment mon; mon = month(CLM_THRU_DT); payment = CLM_PMT_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	PROC SQL;
		CREATE table outpatient_sum as
		SELECT BENE_ID, sum(payment) as payment_outpatient
		FROM temp2
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2; RUN;

	* PDE;
	%IF &year<=2011 %then %let s= saf_file;
	%IF &year>2011 %then %let s= file;
	DATA temp1; set cms.pde_&s._&year; keep BENE_ID SRVC_DT payment mon; mon = month(SRVC_DT); payment = CVRD_D_PLAN_PD_AMT+NCVRD_PLAN_PD_AMT; RUN;
	proc sort data=temp1; by bene_id mon; run;
	data temp2; merge temp1 (in=in1) mbsf_ffs_&year (in=in2); by bene_id mon; if in1 and in2; run;
	PROC SQL;
		CREATE table pde_sum as
		SELECT BENE_ID, sum(payment) as payment_pde
		FROM temp2
		GROUP by BENE_ID;
	QUIT;
	PROC datasets library=work; delete temp1 temp2; RUN;

DATA total_&year; set inpatient_sum SNF_sum HHA_A_sum HHA_B_sum hospice_sum carrier_sum DME1_sum DME2_sum outpatient_sum pde_sum; year=&year; RUN;	
PROC datasets library=work; delete inpatient_sum SNF_sum HHA_A_sum HHA_B_sum hospice_sum carrier_sum DME1_sum DME2_sum outpatient_sum pde_sum; RUN;
%mend MCRPayment;

%macro category(year); 
	PROC SQL;
		CREATE table total_sum_&year as
		SELECT year
			, sum(payment_inpatient) as payment_inpatient, sum(payment_SNF) as payment_SNF, sum(payment_HHA_A) as payment_HHA_A
			, sum(payment_hospice) as payment_hospice, sum(payment_carrier) as payment_carrier 
			, sum(payment_DME_nCBP) as payment_DME_nCBP, sum(payment_DME_CBP) as payment_DME_CBP
			, sum(payment_outpatient) as payment_outpatient, sum(payment_HHA_B) as payment_HHA_B
			, sum(payment_pde) as payment_pde
			, count(payment_inpatient) as Nbene_inpatient, count(payment_SNF) as Nbene_SNF, count(payment_HHA_A) as Nbene_HHA_A
			, count(payment_hospice) as Nbene_hospice, count(payment_carrier) as Nbene_carrier
			, count(payment_DME_nCBP) as Nbene_DME_nCBP, count(payment_DME_CBP) as Nbene_DME_CBP
			, count(payment_outpatient) as Nbene_outpatient, count(payment_HHA_B) as Nbene_HHA_B
			, count(payment_pde) as Nbene_pde
		FROM total_&year
		GROUP by year;
	QUIT;
	* total bene;
	DATA bene1_&year; set total_&year; group = 1; keep BENE_ID group; RUN;
	PROC sort data=bene1_&year NODUPKEY; by BENE_ID; RUN;
	* Part A bene;
	DATA bene2_&year; set total_&year; if payment_inpatient~=. or payment_SNF~=. or payment_HHA_A~=. or payment_hospice~=.; group = 2; keep BENE_ID group; RUN;
	PROC sort data=bene2_&year NODUPKEY; by BENE_ID; RUN;
	* Part B bene;
	DATA bene3_&year; set total_&year; if payment_carrier~=. or payment_DME_nCBP~=. or payment_DME_CBP~=. or payment_outpatient~=. or payment_HHA_B~=.; group = 3; keep BENE_ID group; RUN;
	PROC sort data=bene3_&year NODUPKEY; by BENE_ID; RUN;
	* Part B DME bene;
	DATA bene4_&year; set total_&year; if payment_DME_nCBP~=. or payment_DME_CBP~=.; group = 4; keep BENE_ID group; RUN;
	PROC sort data=bene4_&year NODUPKEY; by BENE_ID; RUN;
	* Part AB bene;
	DATA bene5_&year; set total_&year; if payment_pde=.; group = 5; keep BENE_ID group; RUN;
	PROC sort data=bene5_&year NODUPKEY; by BENE_ID; RUN;

	DATA bene_&year; set bene1_&year bene2_&year bene3_&year bene4_&year bene5_&year; RUN;
	PROC SQL;
		CREATE table bene_sum_&year as
		SELECT group, count(BENE_ID) as N_bene
		FROM bene_&year
		GROUP by group;
	QUIT;
%mend category;

%mbsf(2010);
%MCRPayment(2010);
%category(2010);

%mbsf(2015);
%MCRPayment(2015);
%category(2015);




**************
*** By GEO ***
**************;

%macro mbsf2(year);
%IF &year<=2013 %then %do;
	DATA mbsf_&year; set cms.mbsf_ab_summary_&year; year = &year; 
		rename BENE_AGE_AT_END_REF_YR=age; rename BENE_SEX_IDENT_CD=gender;
		keep year BENE_ID BENE_AGE_AT_END_REF_YR BENE_SEX_IDENT_CD BENE_ZIP_CD BENE_ENTLMT_RSN_CURR BENE_HI_CVRAGE_TOT_MONS BENE_SMI_CVRAGE_TOT_MONS BENE_HMO_CVRAGE_TOT_MONS BENE_STATE_BUYIN_TOT_MONS; 
	RUN;
%END;
%ELSE %DO;
	DATA mbsf_&year; set cms.mbsf_abcd_summary_&year; year = &year; 
		rename ZIP_CD = BENE_ZIP_CD; rename ENTLMT_RSN_CURR=BENE_ENTLMT_RSN_CURR; 
		rename AGE_AT_END_REF_YR=age; rename SEX_IDENT_CD=gender;
		keep year BENE_ID ZIP_CD AGE_AT_END_REF_YR SEX_IDENT_CD ENTLMT_RSN_CURR BENE_HI_CVRAGE_TOT_MONS BENE_SMI_CVRAGE_TOT_MONS BENE_HMO_CVRAGE_TOT_MONS BENE_STATE_BUYIN_TOT_MONS; 
	RUN;
%END;	
%mend mbsf2;

%mbsf2(2010);
DATA mbsf_2010; set mbsf_2010; 
	if BENE_HMO_CVRAGE_TOT_MONS = 0;
	if gender = "1" then male = 1; if gender = "2" then male = 0;
	if BENE_ENTLMT_RSN_CURR = "1" or BENE_ENTLMT_RSN_CURR = "3" then SSDI = 1; if SSDI = . then SSDI = 0;
	if BENE_STATE_BUYIN_TOT_MONS>0 then dual = 1; if dual = . then dual = 0;
	BENE_ZIP5 = substr(BENE_ZIP_CD,1,5);
	keep BENE_ID BENE_ZIP5 age male SSDI dual; 
RUN;
DATA temp; set total_2010; 
	payment = sum(payment_inpatient,payment_SNF,payment_HHA_A,payment_HHA_B,payment_hospice,payment_carrier,payment_DME_nCBP,payment_DME_CBP,payment_outpatient); 
	payment_DME = sum(payment_DME_nCBP,payment_DME_CBP);
	if payment_DME>0 then has_DME = 1;
	if payment_DME_CBP>0 then has_DMECBP = 1;
RUN;
PROC SQL;
	CREATE table total_sum2_2010 as
	SELECT BENE_ID, sum(payment) as payment, sum(payment_DME) as payment_DME, max(has_DME) as has_DME, max(has_DMECBP) as has_DMECBP
	FROM temp
	GROUP by BENE_ID;
QUIT;

PROC sort data=mbsf_2010; by BENE_ID; run;
PROC sort data=total_sum2_2010; by BENE_ID; run;
DATA total_sum2_2010; merge mbsf_2010 (in=in1) total_sum2_2010 (in=in2); by BENE_ID; if in1; RUN;

DATA ZIP_CBA_CBSA; set input.ZIP_CBA_CBSA; rename ZIP = BENE_ZIP5;  RUN;
PROC sort data=total_sum2_2010; by BENE_ZIP5; RUN;
PROC sort data=ZIP_CBA_CBSA; by BENE_ZIP5; RUN;
DATA total_sum2_2010; merge total_sum2_2010 (in = in1) ZIP_CBA_CBSA (in = in2); by BENE_ZIP5; if in1; 
	if payment = . then payment = 0; if payment_DME = . then payment_DME = 0; if has_DME = . then has_DME = 0; if has_DMECBP = . then has_DMECBP = 0; 
RUN;
PROC SQL;
	CREATE table total_sumgeo_2010 as
	SELECT GEO_ID, count(BENE_ID) as n_MCRFFS, mean(age) as age, mean(male) as male, mean(SSDI) as SSDI, mean(dual) as dual, 
	mean(payment) as payment, mean(payment_DME) as payment_DME,	mean(has_DME) as has_DME, mean(has_DMECBP) as has_DMECBP
	FROM total_sum2_2010
	GROUP by GEO_ID;
QUIT;





